package org.testing.dbunit.extension.connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.ext.hsqldb.HsqldbConnection;
import org.dbunit.operation.DatabaseOperation;

public class HsqlDBConnection implements DataBaseConnection {
    public static final DatabaseOperation HSQLDB_SEQUENCE_RESETTER = new DatabaseOperation() {
	@Override
	public void execute(IDatabaseConnection connection, IDataSet dataSet)
		throws DatabaseUnitException, SQLException {
	    String[] tables = dataSet.getTableNames();
	    Statement statement = connection.getConnection().createStatement();
	    for (String table : tables) {
		statement.execute("drop sequence " + table + "_SEQ if exists");
		statement.execute("create sequence " + table + "_SEQ");
	    }
	}
    };
    private static final String PASS = "";
    private static final String USER = "sa";
    private static final String URL = "jdbc:hsqldb:mem:aname";
    private final Connection connection;

    public HsqlDBConnection() {
	try {
	    Class.forName("org.hsqldb.jdbcDriver");
	    connection = DriverManager.getConnection(URL, USER, PASS);

	    role();
	    schema();
	    drops();
	    create();
	} catch (Exception e) {
	    throw new RuntimeException(e);
	}
    }

    private void role() {
	// executeSQL("DROP ROLE dbunitextensionrole");
	// executeSQL("CREATE ROLE dbunitextensionrole");
    }

    private void schema() {
	// executeSQL("CREATE SCHEMA dbunitextension AUTHORIZATION dba");
    }

    private void drops() {
	drop("CONFIG");
	drop("CONFIG_WITH_TIMESTAMP");
    }

    private void drop(String table) {
	executeSQL("drop table " + table + " if exists");
    }

    private void create() {
	executeSQL("create table CONFIG(id integer, name varchar(256))");
	executeSQL("create table CONFIG_WITH_TIMESTAMP(id integer, name varchar(256), date TIMESTAMP)");
    }

    private void executeSQL(String sql) {
	Statement statement = null;
	try {
	    statement = connection.createStatement();
	    int executeUpdate = statement.executeUpdate(sql);
	    if (executeUpdate == -1) {
		throw new RuntimeException("error execute " + sql);
	    }
	} catch (SQLException e) {
	    shutdown();
	    throw Exceptions.cause(e);
	} finally {
	    try {
		if (statement != null) {
		    statement.close();
		}
	    } catch (SQLException e) {
		shutdown();
		throw new RuntimeException(e);
	    }
	}
    }

    public void shutdown() {
	try {
	    if (!connection.isClosed()) {
		Statement st = connection.createStatement();
		st.execute("SHUTDOWN");
		st.close();
		connection.close();
	    }
	} catch (SQLException e) {
	    throw Exceptions.cause(e);
	}
    }

    public IDatabaseConnection getConnection() {
	try {
	    return new HsqldbConnection(connection, null);
	} catch (DatabaseUnitException e) {
	    throw new RuntimeException(e);
	}
    }
}